The Cursor is database object use for getting data and manipulating particular row at a time. It is associated with select query it processes each row returned by select query. When we have to update record we use a cursor. With the help of cursor we can verify every row data and modify it or perform calculations which are not possible when we get all records at once.

It uses following keyword

1-Declare Cursor                

2-Open                             

3-Fetch

4-Close

5-Deallocate 

Declare Cursor-

Syntax for declaring cursor

DECLARE cursor_name CURSOR
 [LOCAL | GLOBAL]  it define cursor scope
 [FORWARD_ONLY | SCROLL]  --it define cursor movements (forward/backward)
 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
 FOR select_statement --define SQL Select statement
 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated 

 Open-

Syntax for opening cursor

It can be of two type

1-Locally

2-Globally

By default it is open locally

OPEN [GLOBAL] cursor_name 
  Open[Local] cursor_Name --it is by defult set

Fetch-

For fetching record we use fetch statement, fetch provide many option for getting record from the cursor.

Syntax is 

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name 

Close-

Close is use for close the cursor

Syntax for close statement

CLOSE cursor_name 

Deallocate

-Deallocate is use for deleting cursor.

Syntax for Deallocate is

DEALLOCATE cursor_name --After deallocation it can not be reopen

 Example

First we create table and name product

CREATE TABLE Product
(
 ProductID int PRIMARY KEY,
 Name varchar (50) NOT NULL,
 Price int NOT NULL,
 
)

Then insert some records.

INSERT INTO Product(ProductID,Name,Price) VALUES(1,'Ramesh',12000)
INSERT INTO Product(ProductID,Name,Price) VALUES(2,'Pavan',25000)
INSERT INTO Product(ProductID,Name,Price) VALUES(3,'Suresht',22000)
INSERT INTO Product(ProductID,Name,Price) VALUES(4,'Sonu',22000)
INSERT INTO Product(ProductID,Name,Price) VALUES(5,'Deepak',28000)

View record

 select * from Product

Complete example of cursor             

DECLARE @Id int
DECLARE @EmpName varchar(50)
DECLARE @ProductID int
DECLARE @Price int
 
 DECLARE
cur_emp CURSOR
STATIC FOR 
SELECT ProductID,Name,Price from Product
OPEN cur_emp
IF @@CURSOR_ROWS
> 0
 BEGIN
 FETCH NEXT FROM cur_emp INTO @ProductID,@EmpName,@Price
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ProductID : '+ convert(varchar(20),@ProductID)+', EmpName : '+@EmpName+ ', Price : '+convert(varchar(20),@Price)
 FETCH NEXT FROM cur_emp INTO @ProductID,@EmpName,@Price
 END
END

Output

You can also check this related site

Implementing Concept of Cursor in SQL Server